Can run on: Windows, Linux
Call DB Procedure/Function
Description
Call DB Procedure/Function is a process entry in the Lookup plugin for a Process Studio. Use the entry to run a database procedure or function once, as a single entry in a process, and to bring the results back into the process.
The entry does not read or write rows. Values arrive as plain text or as PS variables, and the entry writes the returned values into PS variables.
What the entry returns
- When Type is Function, the entry creates one PS variable for the function result. You name the variable in Result name.
- For both procedures and functions, the entry creates one PS variable for every parameter with the direction OUT or INOUT. An OUT parameter only receives a value from the database. An INOUT parameter sends a value to the database and receives a value back in the same variable.
The entry succeeds when the call finishes without an error. The entry fails when the procedure or function name is empty, or when the database returns an error. In both cases, Process Studio writes the reason to the log. For an empty name the log reads "Procedure name is missing or empty. Please specify a valid procedure name.", for a function as well as for a procedure.
If you stop the process while the call is still running, and Abort connection on stop? is selected, Process Studio marks the entry as stopped instead of failed.
When no connection is selected, the entry runs nothing at all and still reports success. Always pick a connection.
Examples:
Example 1:
Procedure: Raise a customer credit limit.
Your database team owns a procedure to raise a credit limit and report the outcome:
SQL:
sp_update_credit_limit(customer_id IN NUMBER,
new_limit IN NUMBER,
status OUT VARCHAR2)
Set up the entry as follows:
- Type: Procedure
- Procedure name: sp_update_credit_limit
- Parameters:
| Name | Direction | Type |
|---|---|---|
| ${Local.customerId} | IN | Integer |
| 50000 | IN | Number |
| Local.status | OUT | String |
Result:
When the process runs, Process Studio creates Local.status, and saves the outcome from the database, for example UPDATED. A later entry reads ${Local.status} and decides whether to send an approval email.
Example 2:
Function: Count open orders
Your database team owns a function to count the open orders for a customer:
SQL:
sp_update_credit_limit(customer_id IN NUMBER,
new_limit IN NUMBER,
status OUT VARCHAR2)
Set up the entry as follows:
- Type: Function
- Function name: fn_get_open_orders
- Result name: Local.openOrders
- Result type: Integer
- Parameters:
| Name | Direction | Type |
|---|---|---|
${Local.customerId} | IN | Integer |
Result:
When the process runs, Local.openOrders holds the count, for example 7. A later entry cpares the count with a limit and puts the new order on hold when the count is too high.
Configurations
| Field Name | Description |
|---|---|
| Process entry name | Specify a unique name for the entry on the canvas. You can add the entry to a process more than once, but each copy needs a unique name. Example: Update customer credit limit The field is mandatory. Leaving it empty shows "Process Entry name cannot be blank" and the dialog stays open. |
| Connection | Select the database connection that runs the procedure or function. The drop-down list shows the connections already defined in the current workflow or process. When only one connection exists, Process Studio selects that connection automatically. Selecting OK while the field is empty shows the message "Please select a valid connection!". The dialog then closes and the entry is saved with no connection, so open the entry again and pick one. The field is mandatory. |
| (Button) Edit… | Click to change the connection selected in Connection. Edit is greyed out until you pick a connection, and stays greyed out for a shared connection. |
| (Button) New… | Click to create a database connection without leaving the step dialog. |
| (Button) Wizard… | Click to create a database connection with the help of guided prompts. |
| Type | Select the type of database call: • Procedure: runs a set of operations in the database and returns values only through OUT and INOUT parameters. • Function: runs a routine and returns one value. Note: The next field changes to Procedure name or Function name to match your choice. |
| Procedure name | Specify the name of the procedure to call. The name must match the name in the database. A wrong name stops the step from connecting to the procedure. The field accepts a static value or a variable and is mandatory. Examples: sp_update_credit_limit, ${Local.procedureName} Note: Procedure name and Function name are the same field. The label changes with Type. Leaving it empty shows "Procedure name cannot be blank" and the dialog stays open. |
| OR | |
| Function Name | Specify the name of the function to call. The name must match the name in the database. A wrong name stops the step from connecting to the function. The field accepts a static value or a variable and is mandatory. Examples: sp_update_credit_limit, ${Local.procedureName} Note: Function name and Procedure name are the same field. The label changes with Type. Leaving it empty shows "Function name cannot be blank" and the dialog stays open. |
| (Button) Find it… | Click to see the procedures and functions available on the selected connection and pick one from the list. Process Studio connects to the database and shows the available routines. Notes: • Find it works with any connection whose driver can list procedures. When the driver returns nothing, the dialog shows "I couldn't find any procedures to select from." • For Microsoft SQL Server connections, the list shows procedures and functions separately, based on the selected Type. |
| Enable auto commit | Select the checkbox to let the database save each statement on its own. Clear the checkbox to leave the commit to the routine. The log then reads "Auto commit turned off, procedures need to handle commit and rollback." The entry runs the call once, so there are no rows to group. Default: Selected |
| Abort connection on stop? | Select the checkbox to close the database connection at once when a user stops the workflow. Use the setting for long-running procedures and heavy queries, where waiting for the current call to finish takes too long. Clear the checkbox to send a normal cancel request and let the active query finish gracefully. Default: Cleared. |
| Result name | Specify the PS variable that holds the value returned by the function. The entry creates the PS variable. The entry has no rows, so nothing is added to a row. The name must start with Local. or Global., otherwise the entry stops with "PS Variables defined in 'Result name' field should start with 'Local.' or 'Global.'". For example: Local.orderId OR Global.orderId. Note: The field is available if Type is selected as Function. The field is mandatory when Type is Function. Leaving it empty shows "Result name cannot be blank" and the dialog stays open. Choosing Procedure clears the field. |
| Result type | Select the data type of the value returned by the function: • Number • String • Date • Boolean • Integer • BigNumber • Binary • Timestamp • Internet Address A type that does not match the return type in the database causes a conversion error when the workflow runs. Default Value: Number. Note: The field is available if Type is selected as Function. |
| Parameters | A procedure sends values back only through parameters with direction OUT or INOUT. A function sends one value back directly, and Result name holds that value. List every parameter that the routine expects in the Parameters section. Add one line per parameter and keep the lines in the same order as the parameters in the database routine. Process Studio matches parameters by position, not by name. A wrong order causes an error or a wrong result when the workflow runs. |
| Name | Select the Local or Global PS variables that supplies the value for the parameter. Example: ${Local.userName} |
| Direction | Select the direction of the parameter: • IN: your process sends a value to the database. In Name, enter a fixed value, such as 1024, or a variable, such as ${Local.orderId}. • OUT: the database sends value back. In Name, enter a PS variable with the scope in front, such as Local.status. Process Studio creates the variable and saves the answer in the variable. • INOUT: your process sends the current value, and the database sends an updated value back into the same variable. In Name, enter a PS variable with the scope in front, such as ${Local.status}. Note: For OUT and INOUT lines, always include the scope prefix (Local. or Global.) at the start of the name. Without the scope, the entry stops with the message Only 'Local' and 'Global' PS Variables are allowed in 'Name' field. |
| Type | Select the kind of value for the parameter. For OUT and INOUT parameters, Process Studio uses the choice to read the answer correctly. When the choice does not match the kind of value in the database, the process stops with a data type error. |